# -*- encoding:utf-8 -*-
from datetime import datetime,timedelta
import json

from sqlalchemy import create_engine
from sqlalchemy.sql import text
from pymongo import Connection
from datetime import datetime

DB_URI = 'mysql://dagger:jH83dap9@125.77.200.20:9527/dagger3?charset=utf8'
DEBUG = True
mongo_con = Connection('125.77.200.3',7017)
mongo_con.dagger.authenticate("dagger","hu267sw9")

sqlite_engine = create_engine('sqlite:///test.db',echo=DEBUG)
def init_db():
    sqlite_engine.execute("""
    create table `usersinfo`(
      id integer primary key autoincrement,
      account string not null,
      length integer not null,
      brand interger default null,
      price interger default null,
      unique(account)
    )
    """)

def get_connection():
    engine = create_engine(DB_URI, echo=DEBUG)
    connection = engine.connect()
    return connection

def get_users_and_hours(start,end):
    conn = get_connection()
    #results = conn.execute('select `customer_account`,sum(_minutes_spent) from `history_channels_0` h '
    #             'where `started` >"%s" and `stopped`<"%s" and `customer_account` in '
    #             '(select `customer_account` from customer_accounts where created <"%s") group by `customer_account`'%
    #             (start,end,start)
    #             )
    results = sqlite_engine.execute("select `account`,`length` from users")
    last_time = datetime(2010,9,8,13,44)
    prices = {'1':20,'2':30,'3':40}
    for row in  results:
        account = str(row['account'])
        s= text("""select `message` from `logs` where `logger` = 'RPC::get_max_bandwidth' 
                and `created` between :d1 and :d2
                and `extra` like :ex  limit 1""")
        brand = conn.execute(s,d1=start,d2=end,ex="%"+account+"%").fetchone()
        try:
            brand = int(brand)/1024
        except:
            if account.endswith("@xmadsl"):
                brand = 6
            elif account.endswith("@xmlan"):
                brand = 20
            else:
                brand = None
        result =mongo_con.dagger.survey201008.find_one({'created':{'$gt':last_time},'account':account},['results.Q5'])
        if result:
            price = prices[result['results']['Q5'][0]]
        else:
            price = None
        if brand and price:
            sqlite_engine.execute('insert into `usersinfo` (`account`,`length`,`brand`,`price`) values ("%s",%d,%d,%d)'%(account,row['length'],brand,price))
            continue
        if brand:
            sqlite_engine.execute('insert into `usersinfo` (`account`,`length`,`brand`) values ("%s",%d,%d)'%(account,row['length'],brand))
            continue
        if price:
            sqlite_engine.execute('insert into `usersinfo` (`account`,`length`,`price`) values ("%s",%d,%d)'%(account,row['length'],price))
            continue
        sqlite_engine.execute('insert into `usersinfo` (`account`,`length`) values ("%s",%d)'%(account,row['length']))


def export_allusers_csv():
    import csv,sys
    csv_writer = csv.writer(open('allusers.csv', 'w'))
    for i in xrange(120):
        result = sqlite_engine.execute("select count(account) from `users` where length=%d"%i).fetchone()
        csv_writer.writerow([i,result[0]])
    result = sqlite_engine.execute("select count(account) from `users` where length>=120").fetchone()
    csv_writer.writerow([120,result[0]])

def export_allusers_json():
    all_users = []
    for i in xrange(120):
        result = sqlite_engine.execute("select count(account) from `users` where length=%d"%i).fetchone()
        all_users.append([i,result[0]])
    result = sqlite_engine.execute("select count(account) from `users` where length>=120").fetchone()
    all_users.append([120,result[0]])
    with open("allusers.json","w") as f:
        json.dump(all_users,f)

def export_20Muser_json():
    pass

if __name__ == '__main__':
    dt1 = datetime(2010,8,1)
    dt2 = datetime(2010,9,1)
    get_users_and_hours(dt1,dt2)
    #export_allusers_json()
    #init_db()
